''' 滴滴打灰后端应用服务 数据库操作API（RMS管理） '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo


logger = logging.getLogger('rmsservice')


# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'])
    return pool.connection()

# 查询附近职位列表
def rmsgetPositionList(param):
    conn = connect_db()
    cur = conn.cursor()
    positionlist = []
    try:
        sql = " select  rcp.id ,  rcp.position_type,  rcp.if_parttime ,  rcp.company_id ,  rcp.post_id ,  rcp.post_name ,  rcp.descr,  rcp.address_name ,  rcp.address_house ,  rcp.address ,  " \
            " rcp.exp_require ,  rcp.min_education ,  rcp.salary_low ,  rcp.salary_up ,  rcp.salary_month ,  rcp.salary ,  rcp.unit,  rcp.unit_money ,  rcp.bonus,  rcp.settlement_type ,  rcp.recruits_number, " \
            " rcp.part_date,  rcp.part_time,  rcp.phone, rcp.skill,  rcp.start_date ,  rcp.end_date ,  rcp.work_days,  rcp.share_number,  rcp.share_money ,  rcp.browse_number ,  rcp.status,  rcp.remark , " \
            " rcp.city,  rcp.citycode ,  rcp.pcity,  rcp.pcitycode ,um.name as memberName,um.avatar as memberAvatar,um.post_name as memberPostName,  rc.name as companyName, rc.staff_size as companyStaffSize, " \
            " rc.nature as companyNatureposition_type, " \
            " ( 6371* acos( cos( radians(" + param['latitude'] + ") ) * cos( radians( rcp.latitude ) ) * cos( radians( rcp.longitude " \
            "   ) - radians(" + param['longitude'] + ") ) + sin( radians(" + param['latitude'] + ") ) * sin( radians( rcp.latitude " \
            "   ) ) ) )  AS distance " \
            "  from rms_company_position rcp " \
            "     left join ums_member um on um.member_id = rcp.create_by and um.if_del = 0 " \
            "     left join rms_company rc on rc.id = rcp.company_id " \
            "  where 1 = 1 " \
            "  and rcp.pcitycode = %s "
        if 'keyword' in param.keys():
            sql += " and (rcp.post_name like '%" + param['keyword'] + "%' or rc.name like '%" + param['keyword'] + "%') "
        if 'postId' in param.keys():
            sql += " and rcp.post_id = " + param['postId']
        if 'industryId' in param.keys():
            sql += " and rc.industry_id = " + param['industryId']        
        if 'salary' in param.keys():
            sql += " and rcp.salary = '" + param['salary'] + "' "
        if 'nature' in param.keys():
            sql += " and rc.nature =  '" + param['nature'] + "' "
        sql += " order by distance asc,rcp.id desc "
            
        cur.execute(sql,(param['pcitycode']))
        for row in cur.fetchall():
            rowdict = {}
            rowdict['id'] = row[0]
            rowdict['positionType'] = row[1]
            rowdict['ifParttime'] = row[2]
            rowdict['companyId'] = row[3]
            rowdict['post_id'] = row[4]
            rowdict['postName'] = row[5]
            rowdict['descr'] = row[6]

            rowdict['address_name'] = row[7]
            rowdict['address_house'] = row[8]
            rowdict['address'] = row[9]
            rowdict['expRequire'] = row[10]
            rowdict['minEducation'] = row[11]
            rowdict['salary_low'] = row[12]
            rowdict['salary_up'] = row[13]

            rowdict['salary_month'] = row[14]
            rowdict['salary'] = row[15]
            rowdict['unit'] = row[16]
            rowdict['unit_money'] = row[17]
            rowdict['bonus'] = row[18]
            rowdict['settlement_type'] = row[19]
            rowdict['recruits_number'] = row[20]

            rowdict['part_date'] = row[21]
            rowdict['part_time'] = row[22]
            rowdict['phone'] = row[23]
            rowdict['skill'] = row[24]
            rowdict['startDate'] = row[25]
            rowdict['endDate'] = row[26]
            rowdict['workDays'] = row[27]

            rowdict['shareNumber'] = row[28]
            rowdict['shareMoney'] = row[29]
            rowdict['browse_number'] = row[30]
            rowdict['status'] = row[31]
            rowdict['remark'] = row[32]
            rowdict['city'] = row[33]

            rowdict['citycode'] = row[34]
            rowdict['pcity'] = row[35]
            rowdict['pcitycode'] = row[36]
            rowdict['memberName'] = row[37]
            rowdict['memberAvatar'] = row[38]
            rowdict['memberPostName'] = row[39]
            rowdict['companyName'] = row[40]

            rowdict['companyStaffSize'] = row[41]
            rowdict['companyNature'] = row[42]
            rowdict['distance'] = row[43]

            positionlist.append(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return positionlist

# 查询职位详细信息
def rmsgetPositionDetail(param):
    conn = connect_db()
    cur = conn.cursor()
    rowdict = {}
    try:
        sql = " select  rcp.id ,  rcp.position_type,  rcp.if_parttime ,  rcp.company_id ,  rcp.post_id ,  rcp.post_name ,  rcp.descr,  rcp.address_name ,  rcp.address_house ,  rcp.address ,  " \
            " rcp.exp_require ,  rcp.min_education ,  rcp.salary_low ,  rcp.salary_up ,  rcp.salary_month ,  rcp.salary ,  rcp.unit,  rcp.unit_money ,  rcp.bonus,  rcp.settlement_type ,  rcp.recruits_number, " \
            " rcp.part_date,  rcp.part_time,  rcp.phone, rcp.skill,  rcp.start_date ,  rcp.end_date ,  rcp.work_days,  rcp.share_number,  rcp.share_money ,  rcp.browse_number ,  rcp.status,  rcp.remark , " \
            " rcp.city,  rcp.citycode ,  rcp.pcity,  rcp.pcitycode ,um.name as memberName,um.avatar as memberAvatar,um.post_name as memberPostName,  rc.name as companyName, rc.staff_size as companyStaffSize, " \
            " rc.nature as companyNatureposition_type ,  rcp.longitude , rcp.latitude, rc.logo, rc.industry_name as industryName, um.member_id as memberId, um.member_sn as memberSn " \
            "  from rms_company_position rcp " \
            "     left join ums_member um on um.member_id=rcp.create_by and um.if_del = 0 " \
            "     left join rms_company rc on rc.id = rcp.company_id " \
            "  where 1 = 1 " \
            "  and rcp.id = %s " 
        cur.execute(sql,(param['id']))
        for row in cur.fetchall():
            rowdict['id'] = row[0]
            rowdict['positionType'] = row[1]
            rowdict['ifParttime'] = row[2]
            rowdict['companyId'] = row[3]
            rowdict['postId'] = row[4]
            rowdict['postName'] = row[5]
            rowdict['descr'] = row[6]

            rowdict['addressName'] = row[7]
            rowdict['addressHouse'] = row[8]
            rowdict['address'] = row[9]
            rowdict['expRequire'] = row[10]
            rowdict['minEducation'] = row[11]
            rowdict['salary_low'] = row[12]
            rowdict['salary_up'] = row[13]

            rowdict['salary_month'] = row[14]
            rowdict['salary'] = row[15]
            rowdict['unit'] = row[16]
            rowdict['unit_money'] = row[17]
            rowdict['bonus'] = row[18]
            rowdict['settlement_type'] = row[19]
            rowdict['recruits_number'] = row[20]

            rowdict['part_date'] = row[21]
            rowdict['part_time'] = row[22]
            rowdict['phone'] = row[23]
            rowdict['skill'] = row[24]
            rowdict['startDate'] = row[25]
            rowdict['endDate'] = row[26]
            rowdict['workDays'] = row[27]

            rowdict['shareNumber'] = row[28]
            rowdict['shareMoney'] = row[29]
            rowdict['browse_number'] = row[30]
            rowdict['status'] = row[31]
            rowdict['remark'] = row[32]
            rowdict['city'] = row[33]

            rowdict['citycode'] = row[34]
            rowdict['pcity'] = row[35]
            rowdict['pcitycode'] = row[36]
            rowdict['memberName'] = row[37]
            rowdict['memberAvatar'] = row[38]
            rowdict['memberPostName'] = row[39]
            rowdict['companyName'] = row[40]

            rowdict['companyStaffSize'] = row[41]
            rowdict['companyNature'] = row[42]

            rowdict['longitude'] = row[43]
            rowdict['latitude'] = row[44]
            rowdict['companyLogo'] = row[45]
            rowdict['industryName'] = row[46]
            rowdict['memberId'] = row[47]
            rowdict['memberSn'] = row[48]

        rowdict['ifApply'] = False
        rowdict['ifCollect'] = False
        if param['memberId'] is not None:
            sql = " select id from wms_collect where member_id = %s and position_id = %s limit 1 "
            cur.execute(sql,(param['memberId'],param['id']))
            for row in cur.fetchall():
                rowdict['ifCollect'] = True

            sql = " select id from wms_apply_log where member_id = %s and position_id = %s limit 1 "
            cur.execute(sql,(param['memberId'],param['id']))
            for row in cur.fetchall():
                rowdict['ifApply'] = True
        logger.info(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rowdict

# 查询公司职位列表
def rmsgetCompanyPositionList(param):
    conn = connect_db()
    cur = conn.cursor()
    positionlist = []
    try:
        sql = " select  rcp.id ,  rcp.position_type,  rcp.if_parttime ,  rcp.company_id ,  rcp.post_id ,  rcp.post_name ,  rcp.descr,  rcp.address_name ,  rcp.address_house ,  rcp.address ,  " \
            " rcp.exp_require ,  rcp.min_education ,  rcp.salary_low ,  rcp.salary_up ,  rcp.salary_month ,  rcp.salary ,  rcp.unit,  rcp.unit_money ,  rcp.bonus,  rcp.settlement_type ,  rcp.recruits_number, " \
            " rcp.part_date,  rcp.part_time,  rcp.phone, rcp.skill,  rcp.start_date ,  rcp.end_date ,  rcp.work_days,  rcp.share_number,  rcp.share_money ,  rcp.browse_number ,  rcp.status,  rcp.remark , " \
            " rcp.city,  rcp.citycode ,  rcp.pcity,  rcp.pcitycode ,um.name as memberName,um.avatar as memberAvatar,um.post_name as memberPostName,  rc.name as companyName, rc.staff_size as companyStaffSize, " \
            " rc.nature as companyNature " \
            "  from rms_company_position rcp " \
            "     left join ums_member um on um.member_id=rcp.create_by and um.if_del = 0 " \
            "     left join  rms_company rc on rc.id = rcp.company_id " \
            "  where 1 = 1 "
        if 'id' in param.keys():
            sql += "  and rcp.id = " + str(param['id'])
        if 'companyId' in param.keys():
            sql += "  and rcp.company_id = " + str(param['companyId'])
        if 'status' in param.keys():
            if param['status'] != '0':
                sql += "  and rcp.status = " + str(param['status'])
        if 'positionType' in param.keys():
            sql += "  and rcp.position_type = " + str(param['positionType'])
        logger.info(sql)
        cur.execute(sql)
        for row in cur.fetchall():
            rowdict = {}
            rowdict['id'] = row[0]
            rowdict['positionType'] = row[1]
            rowdict['ifParttime'] = row[2]
            rowdict['companyId'] = row[3]
            rowdict['post_id'] = row[4]
            rowdict['postName'] = row[5]
            rowdict['descr'] = row[6]

            rowdict['address_name'] = row[7]
            rowdict['address_house'] = row[8]
            rowdict['address'] = row[9]
            rowdict['expRequire'] = row[10]
            rowdict['minEducation'] = row[11]
            rowdict['salary_low'] = row[12]
            rowdict['salary_up'] = row[13]

            rowdict['salary_month'] = row[14]
            rowdict['salary'] = row[15]
            rowdict['unit'] = row[16]
            rowdict['unit_money'] = row[17]
            rowdict['bonus'] = row[18]
            rowdict['settlement_type'] = row[19]
            rowdict['recruits_number'] = row[20]

            rowdict['part_date'] = row[21]
            rowdict['part_time'] = row[22]
            rowdict['phone'] = row[23]
            rowdict['skill'] = row[24]
            rowdict['startDate'] = row[25]
            rowdict['endDate'] = row[26]
            rowdict['workDays'] = row[27]

            rowdict['shareNumber'] = row[28]
            rowdict['shareMoney'] = row[29]
            rowdict['browse_number'] = row[30]
            rowdict['status'] = row[31]
            rowdict['remark'] = row[32]
            rowdict['city'] = row[33]

            rowdict['citycode'] = row[34]
            rowdict['pcity'] = row[35]
            rowdict['pcitycode'] = row[36]
            rowdict['memberName'] = row[37]
            rowdict['memberAvatar'] = row[38]
            rowdict['memberPostName'] = row[39]
            rowdict['companyName'] = row[40]

            rowdict['companyStaffSize'] = row[41]
            rowdict['companyNature'] = row[42]

            positionlist.append(rowdict)
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return positionlist

# 查询公司信息
def getCompanyDetail(param):
    conn = connect_db()
    cur = conn.cursor()
    rowdict = {}
    try:
        if param['id'] is not None:
            sql = " select id AS id, logo AS logo,start_time AS startTime,browse_number AS browseNumber,add_auth_time AS addAuthTime,end_time AS endTime,rest_time AS restTime,work_overtime AS workOvertime, name AS name " \
                " ,welfare AS welfare ,album AS album,address_name AS addressName,address_house AS addressHouse,industry_name AS industryName, full_name AS fullName, industry_id AS industryId, staff_size AS staffSize " \
                " , company_profile AS companyProfile, business_license AS businessLicense, address AS address, longitude AS longitude, latitude AS latitude, verify_status AS verifyStatus, refuse_reason AS refuseReason " \
                " ,nature AS nature, real_name AS realName, legal_person AS legalPerson, identity AS identity, auth_date AS authDate, city AS city, citycode AS citycode, pcity AS pcity, pcitycode AS pcitycode,status AS status " \
                " , remark AS remark from rms_company where 1 = 1 AND id = %s " 
            cur.execute(sql,(param['id']))
        else:
            sql = " select id AS id, logo AS logo,start_time AS startTime,browse_number AS browseNumber,add_auth_time AS addAuthTime,end_time AS endTime,rest_time AS restTime,work_overtime AS workOvertime, name AS name " \
                " ,welfare AS welfare ,album AS album,address_name AS addressName,address_house AS addressHouse,industry_name AS industryName, full_name AS fullName, industry_id AS industryId, staff_size AS staffSize " \
                " , company_profile AS companyProfile, business_license AS businessLicense, address AS address, longitude AS longitude, latitude AS latitude, verify_status AS verifyStatus, refuse_reason AS refuseReason " \
                " ,nature AS nature, real_name AS realName, legal_person AS legalPerson, identity AS identity, auth_date AS authDate, city AS city, citycode AS citycode, pcity AS pcity, pcitycode AS pcitycode,status AS status " \
                " , remark AS remark from rms_company where 1 = 1 AND id in (select company_id from ums_member where if_del = 0 and member_id = %s ) " 
            cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            rowdict['id'] = row[0]
            rowdict['logo'] = row[1]
            rowdict['startTime'] = row[2]
            rowdict['browseNumber'] = row[3]
            rowdict['addAuthTime'] = row[4]
            rowdict['endTime'] = row[5]
            rowdict['restTime'] = row[6]

            rowdict['workOvertime'] = row[7]
            rowdict['name'] = row[8]
            rowdict['welfare'] = row[9]
            rowdict['album'] = row[10]
            rowdict['addressName'] = row[11]
            rowdict['addressHouse'] = row[12]
            rowdict['industryName'] = row[13]

            rowdict['fullName'] = row[14]
            rowdict['industryId'] = row[15]
            rowdict['staffSize'] = row[16]
            rowdict['companyProfile'] = row[17]
            rowdict['businessLicense'] = row[18]
            rowdict['address'] = row[19]
            rowdict['longitude'] = row[20]

            rowdict['latitude'] = row[21]
            rowdict['verifyStatus'] = row[22]
            rowdict['refuseReason'] = row[23]
            rowdict['nature'] = row[24]
            rowdict['realName'] = row[25]
            rowdict['legalPerson'] = row[26]
            rowdict['identity'] = row[27]

            rowdict['authDate'] = row[28]
            rowdict['city'] = row[29]
            rowdict['citycode'] = row[30]
            rowdict['pcity'] = row[31]
            rowdict['pcitycode'] = row[32]
            rowdict['status'] = row[33]
            rowdict['remark'] = row[34]

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rowdict

# 查询公司列表(字段精简)
def getCompanyListForSelect(param):
    conn = connect_db()
    cur = conn.cursor()
    companylist = []
    try:
        sql = " select id AS id,name as name, logo AS logo,nature AS nature,industry_name AS industryName, full_name AS fullName, industry_id AS industryId,staff_size AS staffSize, verify_status as verifyStatus " \
              "from rms_company where 1 = 1 and (name like CONCAT('%" + param['keyword'] + "%') or full_name like CONCAT('%" + param['keyword'] + "%')) "
            
        cur.execute(sql)
        for row in cur.fetchall():
            rowdict = {}
            rowdict['id'] = row[0]
            rowdict['name'] = row[1]
            rowdict['logo'] = row[2]
            rowdict['nature'] = row[3]
            rowdict['industryName'] = row[4]
            rowdict['fullName'] = row[5]
            rowdict['industryId'] = row[6]
            rowdict['staffSize'] = row[7]
            rowdict['verifyStatus'] = row[8]

            companylist.append(rowdict)

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return companylist

# 获取公司ID
def getCompanyId(companyid):
    conn = connect_db()
    cur = conn.cursor()
    rec = ''
    try:         
        sql = " select company_id from ums_member where member_id = %s "
        cur.execute(sql,(companyid))
        for row in cur.fetchall():
            rec = row[0]

    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rec


# 新增/更新公司信息
def updateCompany(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        if 'id' in param.keys():
            sql = "update rms_company set logo = %s, name = %s, full_name = %s, industry_id = %s, industry_name = %s, staff_size = %s, nature = %s, update_by = %s, update_time = %s " \
                  "where id = %s "
            cnt = cur.execute(sql,(param['logo'],param['name'],param['fullName'],param['industryId'],param['industryName'],param['staffSize'],param['nature'],param['memberId']
                                       ,datetime.datetime.now(),param['id']))
        else:
            sql = "INSERT INTO rms_company ( logo, name, full_name, industry_id, industry_name, staff_size, nature, create_by, create_time ) values (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(param['logo'],param['name'],param['fullName'],param['industryId'],param['industryName'],param['staffSize'],param['nature'],param['memberId']
                                   ,datetime.datetime.now()))
            conn.commit()
            sql = "select id from rms_company where full_name = %s "
            cur.execute(sql,(param['fullName']))
            for row in cur.fetchall():
                param['id'] = row[0]
        
        sql = " UPDATE ums_member SET company_id = %s, company = %s, post_name = %s, update_time = %s WHERE member_id = %s  "
        cnt = cur.execute(sql,(param['id'],param['name'],param['postName'],datetime.datetime.now(),param['memberId']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 公司认证
def updateAuth(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        if 'id' in param.keys():
            sql = "update rms_company set business_license = %s, real_name = %s, legal_person = %s, identity = %s, verify_status = %s, update_by = %s, update_time = %s " \
                  "where id = %s "
            cnt = cur.execute(sql,(param['businessLicense'],param['realName'],param['legalPerson'],param['identity'],param['verifyStatus'],param['memberId'],datetime.datetime.now(),param['id']))
        else:
            sql = "update rms_company set business_license = %s, real_name = %s, legal_person = %s, identity = %s, verify_status = %s, update_by = %s, update_time = %s " \
                  "where member_id = %s "
            cnt = cur.execute(sql,(param['businessLicense'],param['realName'],param['legalPerson'],param['identity'],param['verifyStatus'],param['memberId'],datetime.datetime.now(),param['memberId']))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 新增/更新公司信息（详细）
def updateCompanyDetails(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        if 'id' in param.keys():
            sql = "update rms_company set logo = %s, name = %s, full_name = %s, industry_id = %s, industry_name = %s, staff_size = %s, nature = %s, rest_time = %s, work_overtime = %s " \
                  ", welfare = %s, start_time = %s, end_time = %s, address_name = %s, address = %s, address_house = %s, latitude = %s, longitude = %s, city = %s, citycode = %s, pcity = %s " \
                  ", pcitycode = %s, company_profile = %s, album = %s, update_by = %s, update_time = %s " \
                  "where id = %s "
            cnt = cur.execute(sql,(param['logo'],param['name'],param['fullName'],param['industryId'],param['industryName'],param['staffSize'],param['nature'],param['restTime'],param['workOvertime']
                                   ,param['welfare'],param['startTime'],param['endTime'],param['addressName'],param['address'],param['addressHouse'],param['latitude'],param['longitude'],param['city']
                                   ,param['citycode'],param['pcity'],param['pcitycode'],param['companyProfile'],param['album'],param['memberId'],datetime.datetime.now(),param['id']))
        else:
            sql = "INSERT INTO rms_company ( logo, name, full_name, industry_id, industry_name, staff_size, nature, rest_time, work_overtime " \
                  ", welfare, start_time, end_time, address_name, address, address_house, latitude, longitude, city, citycode, pcity " \
                  ", pcitycode, company_profile, album, create_by, create_time ) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(param['logo'],param['name'],param['fullName'],param['industryId'],param['industryName'],param['staffSize'],param['nature'],param['restTime'],param['workOvertime']
                                   ,param['welfare'],param['startTime'],param['endTime'],param['addressName'],param['address'],param['addressHouse'],param['latitude'],param['longitude'],param['city']
                                   ,param['citycode'],param['pcity'],param['pcitycode'],param['companyProfile'],param['album'],param['memberId']
                                   ,datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 新增/更新职位信息
def updatePosition(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        if 'id' in param.keys():
            sql = "update rms_company_position set position_type = %s,if_parttime = %s,post_id = %s,post_name = %s,min_education = %s,exp_require = %s,phone = %s,salary = %s,descr = %s,address_name = %s " \
                  ",address = %s,address_house = %s,latitude = %s,longitude = %s,start_date = %s,end_date = %s,share_number = %s,skill = %s,update_by = %s,update_time = %s where id = %s " 
            cnt = cur.execute(sql,(param['positionType'],param['ifParttime'],param['postId'],param['postName'],param['minEducation'],param['expRequire'],param['phone'],param['salary'],param['descr']
                                   ,param['addressName'],param['address'],param['addressHouse'],param['latitude'],param['longitude'],param['startDate'],param['endDate'],param['shareNumber'],param['skill']
                                   ,param['memberId'],datetime.datetime.now(),param['id']))
        else:
            sql = "INSERT INTO rms_company_position ( position_type,if_parttime,post_id,post_name,min_education,exp_require,phone,salary,descr,address_name,address,address_house,latitude,longitude,start_date " \
                  ",end_date,share_number,skill,company_id,create_by,create_time ) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(param['positionType'],param['ifParttime'],param['postId'],param['postName'],param['minEducation'],param['expRequire'],param['phone'],param['salary'],param['descr']
                                   ,param['addressName'],param['address'],param['addressHouse'],param['latitude'],param['longitude'],param['startDate'],param['endDate'],param['shareNumber'],param['skill'],param['companyId'],param['memberId']
                                   ,datetime.datetime.now()))
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 更新职位状态
def updatePositionStatus(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "update rms_company_position set status = %s,update_by = %s,update_time = %s where id = %s " 
        cnt = cur.execute(sql,(param['status'],param['memberId'],datetime.datetime.now(),param['id']))

        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt

# 删除公司职位
def deletePositionById(param):
    conn = connect_db()
    cur = conn.cursor()
    cnt = 0
    try:
        sql = "delete from rms_company_position where id = %s " 
        cnt = cur.execute(sql,(param['id']))

        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return cnt
